import json
from contextlib import contextmanager
from datetime import datetime
from typing import Optional, Dict, List

import pymysql

from backend.config.settings import settings
from backend.models.user_model import User


# 数据库连接池
def get_db_connection():
    return pymysql.connect(
        host=settings.DB_HOST,
        port=settings.DB_PORT,
        user=settings.DB_USER,
        password=settings.DB_PASSWORD,
        database=settings.DB_NAME,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor
    )

# 数据库操作上下文管理器
@contextmanager
def get_db_cursor():
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        yield cursor
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

# 管理员操作
def get_all_detection_records() -> Optional[List[Dict]]:
    """
    获取所有检测记录,按用户分组
    """
    with get_db_cursor() as cursor:
        query =  """SELECT u.id AS user_id,u.username,d.id AS record_id,d.left_image_path,d.right_image_path,d.sex,d.age,d.prediction,d.created_at 
                    FROM detection_records d 
                    JOIN `user` u ON d.user_id = u.id
                    ORDER BY u.id, d.created_at DESC"""
        cursor.execute(query)
        records = cursor.fetchall()
        return records

def get_all_users(role_filter: Optional[str] = None) -> Optional[List[Dict]]:
    """
    获取所有用户，可以按角色筛查
    """
    with get_db_cursor() as cursor:
        query = "SELECT id, username, email, role, created_at FROM `user`"
        params = []
        if role_filter:
            query += " WHERE role = %s"
            params.append(role_filter)
        query += " ORDER BY id ASC"
        cursor.execute(query, params)
        users = cursor.fetchall()
        return users

def get_today_detection_records_count():
    """
    获取今天的检测记录数量
    """
    today = datetime.now().strftime("%Y-%m-%d")
    with get_db_cursor() as cursor:
        query = "SELECT COUNT(*) as count FROM detection_records WHERE DATE(created_at) = %s"
        cursor.execute(query, (today,))
        data = cursor.fetchone()
        count = data["count"] if data else 0
        return count

def delete_user_and_records(username: str) ->Optional[Dict]:
    """
    删除用户以及与其相关联的检测记录
    """
    # print("删除用户：", username)
    user_id = get_user_by_username(username)["id"]
    # print(user_id)
    with get_db_cursor() as cursor:
        # 先删除用户的检测记录
        delete_records_query = "DELETE FROM detection_records WHERE user_id = %s"
        cursor.execute(delete_records_query, (user_id,))
        # 再删除用户
        delete_user_query = "DELETE FROM user WHERE username = %s"
        cursor.execute(delete_user_query, (username,))
        return {"message": f"用户{username}及其检测记录删除成功"}


# 用户操作函数
def get_user_by_username(username: str)-> Optional[User]:
    """
    根据用户名查询用户信息
    """
    with get_db_cursor() as cursor:
        query = "SELECT * FROM user WHERE username = %s"
        cursor.execute(query, (username,))
        user_data = cursor.fetchone()
        # print("查询结果：", cursor.fetchone(),user_data)
        return user_data

def create_user(username: str, password_hash: str, email: str, role: str)-> int:
    """
    创建用户
    """
    with get_db_cursor() as cursor:
        query = "INSERT INTO user (username, password, email, role) VALUES (%s, %s, %s, %s)"
        cursor.execute(query, (username, password_hash, email,  role))
        return cursor.lastrowid

# 检测记录表
def create_detection_record(user_id: int, left_image_path: str, right_image_path: str, prediction: Optional[Dict] = None,sex: str = None,age: int = None) -> Optional[ int]:
    """
    创建检测记录
    """
    if user_id == 0:
        return None
    with get_db_cursor() as cursor:
        query = "INSERT INTO detection_records (user_id, left_image_path,right_image_path, prediction,sex,age, created_at) VALUES (%s, %s, %s, %s, %s, %s,NOW())"
        # 把prediction转换为JSON字符串，因为数据库中存储的是JSON字符串
        prediction_json = json.dumps(prediction) if prediction else None
        cursor.execute(query, (user_id, left_image_path,right_image_path, prediction_json,sex,age))
        return cursor.lastrowid

def get_detection_record_by_user(user_id: int) -> List[Dict]:
    """
    根据用户ID获取检测记录
    """
    records = []
    with get_db_cursor() as cursor:
        query = "SELECT * FROM detection_records WHERE user_id = %s ORDER BY created_at DESC"
        cursor.execute(query, (user_id,))
        # 把prediction从JSON字符串转换为字典
        for row in cursor.fetchall():
            if row.get("prediction"):
                row["prediction"] = json.loads(row["prediction"])
            records.append(dict( row))
        return records

def update_detection_record(record_id: int, prediction: Dict):
    """
    更新检测记录
    """
    with get_db_cursor() as cursor:
        query = "UPDATE detection_records SET prediction = %s WHERE id = %s"
        cursor.execute(query, (prediction, record_id))